The I in ACID

Postgres Vision
2020-06-24

Image by Jill Wellington from Pixabay

Who am I

Image by Anemone123 from Pixabay

The I in ACID

  • ACID
  • Transaction Isolation
  • Use cases
Image by Shameer Pk from Pixabay

ACID

  • Atomicity
  • Consistency
  • Isolation
  • Durability
Image by congerdesign from Pixabay

Atomicity

An atomic transaction is an indivisible and irreducible series of database operations such that either all occur, or nothing occurs.
Wikipedia
Image by MasterTux from Pixabay

Consistency

Consistency in database systems refers to the requirement that any given database transaction must change affected data only in allowed ways.
Wikipedia
Image by Magnascan from Pixabay

Isolation

In database systems, isolation determines how transaction integrity is visible to other users and systems.
Wikipedia
Image by Klaus Hausmann from Pixabay

Durability

In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently.
Wikipedia
Image by Steve Buissinne from Pixabay

Isolation levels

Image by Peter H from Pixabay

Read uncommitted

One transaction may see not-yet-committed changes made by other transactions.
Wikipedia
PostgreSQL's Read Uncommitted mode behaves like Read Committed.
Postgres documentation
Image by Peter H from Pixabay

Read uncommitted

If the transaction isolation level of the SQL-transaction is READ UNCOMMITTED, then any <level of isolation> is permissible.
SQL 2016 standard
Image by Peter H from Pixabay

Read committed

Read committed is an isolation level that guarantees that any data read is committed at the moment it is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read.
Wikipedia
Image by Peter H from Pixabay

Repeatable read

The Repeatable Read isolation level only sees data committed before the transaction began.
Postgres documentation
Image by Peter H from Pixabay

Serializable

This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.
Postgres documentation
Image by Peter H from Pixabay


Isolation anomalies

Image by Pamela Palacio from Pixabay

Dirty read

A transaction reads data written by a concurrent uncommitted transaction.
Postgres documentation
Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Lost update


A data integrity problem in which one writer of data overwrites the changes of a different writer modifying the same data.
Oracle documentation (Database Concepts)
Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Non-repeatable read


A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
Wikipedia
Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
Postgres documentation
Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Serialization anomaly

The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.
Postgres documentation
Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Serialization anomaly

Image by Rudy and Peter Skitterians from Pixabay

Anomalies and isolation levels

Isolation Level Read uncommitted Read committed Repeatable read Serializable
Dirty Read
Lost update
Non-repeatable Read
Phantom Read
Serialization Anomaly
Image by Rudy and Peter Skitterians from Pixabay

Anomalies and isolation levels

(For Postgres)
Isolation Level Read uncommitted Read committed Repeatable read Serializable
Dirty Read
Lost update
Non-repeatable Read
Phantom Read
Serialization Anomaly
Image by Rudy and Peter Skitterians from Pixabay

MVCC

Multiversion concurrency control (MCC or MVCC), is a concurrency control method commonly used by database management systems to provide concurrent access to the database.
Wikipedia
Image by 272447 from Pixabay

Locks

Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.
Wikipedia
Image by Heidelbergerin from Pixabay

Dead lock

A deadlock is a state in which each member of a group is waiting for another member, including itself, to take action, such as sending a message or more commonly releasing a lock.
Wikipedia
Image by Heidelbergerin from Pixabay

Dead lock

Image by Heidelbergerin from Pixabay

Dead lock

Image by Heidelbergerin from Pixabay

Dead lock

Image by Heidelbergerin from Pixabay

Dead lock

Image by Heidelbergerin from Pixabay

Dead lock

Image by Heidelbergerin from Pixabay

How to use that

    Is it ok to have...
  • Dirty reads ?
  • Lost updates ?
  • Non-repeatable reads ?
  • Phantom reads ?
  • Serialization anomalies ?
Image by Boskampi from Pixabay

Most of the time

    Is it ok to have...
  • Dirty reads ? Probably not
  • Lost updates ? Probably
  • Non-repeatable reads ? Probably
  • Phantom reads ? Probably
  • Serialization anomalies ? Probably
Image by Jakub Luksch from Pixabay

Most of the time

→ Read committed

Image by Jakub Luksch from Pixabay

Pagination

    Is it ok to have...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? It depends
  • Phantom reads ? Probably
  • Serialization anomalies ? Probably
Image by Linus Schütz from Pixabay

Pagination

→ Read committed or Repeatable read

Image by Linus Schütz from Pixabay

Inventory level

    Is it ok to have...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? Probably not
  • Phantom reads ? Probably not
  • Serialization anomalies ? Probably
Image by Pexels from Pixabay

Inventory level

→ Repeatable read

Image by Pexels from Pixabay

Invoice numbers

    Is it ok to have...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? Probably not
  • Phantom reads ? Probably not
  • Serialization anomalies ? Probably not
Image by Oliver Menyhart from Pixabay

Invoice numbers

→ Serializable

Image by Oliver Menyhart from Pixabay

To conclude

Image by Michitogo from Pixabay

Question time!

Image by Arek Socha from Pixabay